﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Data;
using System.Collections;
using DTO;
namespace DAO
{
    public class MayBayDAO
    {
          DataTable dt;
        List<string> dsThamSo;
        ArrayList dsGiaTri;
        public MayBayDAO()
        {
            dt = new DataTable();
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            
        }
        public DataTable layThongTinMayBay()
        {
            dt = DataProvider.ExecuteStoreProc("sp_layThongTinMayBay",null,null);
            return dt;

        }
        public DataTable layThongTinLishSuMayBay(string soHieuMayBay)
        {
            dsThamSo.Add("@soHieu");
            dsGiaTri.Add(soHieuMayBay);
            dt = DataProvider.ExecuteStoreProc("sp_layThongTinLishSuMayBay", dsThamSo, dsGiaTri);
            return dt;

        }
        public DataTable layThongTinSoHieuMayBay()
        {
            dt = DataProvider.ExecuteStoreProc("sp_laySoHieuMayBay", null, null);
            return dt;

        }
        public DataTable layThongTinSoHieuMayBay(string ngayDi,int changBay)

        {
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@ngayDi");
            dsThamSo.Add("@maChangBay");
            dsGiaTri.Add(ngayDi);
            dsGiaTri.Add(changBay);
            dt = DataProvider.ExecuteStoreProc("sp_loadSoHieuMayBayTheoChangBay", dsThamSo, dsGiaTri);
            return dt;

        }
        public int capNhatMayBay(string maMayBay)
        {
            int n = 0;
            string sql = "update MayBay set tinhTrang = 'True' where ID_mayBay = '" + maMayBay + "'";
            n = DataProvider.ExecuteNoneQuery(sql);
            return n;
        }
        public int capNhatMayBay(MayBayDTO dto,int soChoHang1,int soChoHang2,int soChoHang3)
        {
            int n = 0;
            int n1 = 0;
            int n2 = 0;
            int n3 = 3;
            string sql = "update MayBay set hangMayBay = '"+dto.HangMayBay+"', ";
            sql += "ngayNhap = '"+dto.NgayNhap+"', giaNhap =" + dto.GiaNhap+", soChoNgoi = "+dto.SoChoNgoi+", soKmDaBay = " +dto.SoKmDaBay+", ";
            sql += "taiTrong = "+dto.TaiTrong+" where ID_mayBay = '" + dto.MaMayBay + "'";
            n = DataProvider.ExecuteNoneQuery(sql);
            string sql1 = "update HangGhe set soCho =" + soChoHang1 + " where ID_mayBay= '" + dto.MaMayBay + "' and tenHangGhe = N'Thuong Nhan'";
            n1 = DataProvider.ExecuteNoneQuery(sql1);
            string sql2 = "update HangGhe set soCho =" + soChoHang2 + " where ID_mayBay= '" + dto.MaMayBay + "' and tenHangGhe = N'Pho Thong'";
            n2 = DataProvider.ExecuteNoneQuery(sql2);
            string sql3 = "update HangGhe set soCho =" + soChoHang3 + " where ID_mayBay= '" + dto.MaMayBay + "' and tenHangGhe = N'Tiet Kiem'";
            n3 = DataProvider.ExecuteNoneQuery(sql3);

            return n + n1 + n2 + n3;
        }
        public bool kiemTraSoHieu(string soHieu)
        {
            dsThamSo.Add("@soHieu");
            dsGiaTri.Add(soHieu);
            dt = DataProvider.ExecuteStoreProc("sp_layIDMayBay", dsThamSo, dsGiaTri);
            if (dt.Rows.Count > 0)
                return false;
            return true;
        }
        public int layIDMayBay(string soHieuMayBay)
        {
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            
            dsThamSo.Add("@soHieu");
            dsGiaTri.Add(soHieuMayBay);
            dt = DataProvider.ExecuteStoreProc("sp_layIDMayBay", dsThamSo, dsGiaTri);
            return int.Parse(dt.Rows[0]["ID_mayBay"].ToString());

        }
        public int themMayBay(MayBayDTO dto,int soChoHang1,int soChoHang2,int soChoHang3)
        {
            int n = 0;
            int n1 = 0;
            int n2 = 0;
            int n3 = 0;

            string sql = "insert into MayBay(hangMayBay,soHieuMayBay,ngayNhap,taiTrong,giaNhap,doViTaiTrong,donViGiaNhap,soChoNgoi,soKmDaBay,tinhTrang) ";
            sql += "values('" + dto.HangMayBay + "','" + dto.SoHieuMayBay + "', ";
            sql += "'" + dto.NgayNhap + "'," + dto.TaiTrong + ", " + dto.GiaNhap + ",N'Tấn',N'USD', " + dto.SoChoNgoi + ", " + dto.SoKmDaBay + ",'False')";
            n = DataProvider.ExecuteNoneQuery(sql);

            /////////////////
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@soHieu");
            dsGiaTri.Add(dto.SoHieuMayBay);
            dt = DataProvider.ExecuteStoreProc("sp_layIDMayBay", dsThamSo, dsGiaTri);
            
            if(dt.Rows.Count > 0)
            {
                string maMayBay = dt.Rows[0]["ID_mayBay"].ToString();
            string sql1 = "insert into HangGhe(ID_mayBay,tenHangGhe,soCho) values(" + maMayBay + ",N'Thuong Nhan'," + soChoHang1 + ")";
            n1 = DataProvider.ExecuteNoneQuery(sql1);
            string sql2 = "insert into HangGhe(ID_mayBay,tenHangGhe,soCho) values(" + maMayBay + ",N'Pho Thong'," + soChoHang2 + ")";
            n2 = DataProvider.ExecuteNoneQuery(sql2);
            string sql3 = "insert into HangGhe(ID_mayBay,tenHangGhe,soCho) values(" + maMayBay + ",N'Tiet Kiem'," + soChoHang3 + ")";
            n3 = DataProvider.ExecuteNoneQuery(sql3);
            }
            return n + n1 + n2 + n3;
        }
        public int themLichSuBay(string soHieuMayBay, string tuNgay, string denNgay, string noiDung, int tinhTrangMayBay, string ViTri)
        {
             /////////////////
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@soHieu");
            dsGiaTri.Add(soHieuMayBay);
            dt = DataProvider.ExecuteStoreProc("sp_layIDMayBay", dsThamSo, dsGiaTri);

            int n = 0;
            if (dt.Rows.Count > 0)
            {
                string maMayBay = dt.Rows[0]["ID_mayBay"].ToString();
                
                string sql = "insert into LichSuBay(ID_mayBay,tuNgay,denNgay,noiDung,tinhTrangMayBay,viTri,tinhTrang)";
                sql += " values(" + maMayBay + ",'" + tuNgay + "','" + denNgay + "',N'" + noiDung + "'," + tinhTrangMayBay + ",N'" + ViTri + "','False')";
                n = DataProvider.ExecuteNoneQuery(sql);
            }
            return n;
        }
        public DataTable laySoCho(string soHieuMayBay)
        {
            dt = new DataTable();
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@soHieuMayBay");
            dsGiaTri.Add(soHieuMayBay);
            dt = DataProvider.ExecuteStoreProc("sp_laySoChoTrongMoiHangGhe", dsThamSo, dsGiaTri);
            return dt;
        }
        public int layIDLichSuBay(string ngayDi, int changBay)
        {
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@ngayDi");
            dsThamSo.Add("@maChangBay");
            dsGiaTri.Add(ngayDi);
            dsGiaTri.Add(changBay);
            dt = DataProvider.ExecuteStoreProc("sp_loadIDLichSuTheoChangBay", dsThamSo, dsGiaTri);
            return int.Parse(dt.Rows[0]["ID"].ToString());

        }
        public int capNhatLichSuBay(int ID)
        {
            int n = 0;
            string sql = "update LichSuBay set tinhTrang = 'True' where ID = " + ID + "";
            n = DataProvider.ExecuteNoneQuery(sql);
            return n;
        }
        
        
    }
}
